﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using DeqiYQ.SQLConn;
using NPOI.SS.Util;
using NPOI.SS.Converter;
using NPOI.XSSF.Util;
using System.Drawing;
using System.Web.Mvc;
using DeqiYQ.Controllers;

namespace DeqiYQ.SQLConn
{
    public class ExcelTool
    {
        BaseDao dao = new BaseDao();

        public XSSFWorkbook DeviceDataSheet(string StartTime, string EndTime,string MN)
        {
            string temp = "";
            int currRow = 0;
            int tempRow = 0;
            double temp1 = 0;
            int i1 = 0;
            

            XSSFWorkbook book = new XSSFWorkbook();
            ISheet Sheet1 = book.CreateSheet("数据表");


            XSSFCellStyle fCellStyle = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = book.CreateDataFormat();
            //fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式,#表示空位保留
            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;

            //YXZB
            string sql = "select a.Name,a.Address,CONVERT(varchar,b.DataTime,120) as LastTime,Y1,V1,K1,Y2,V2,K2" +
                " FROM t_d_Device a INNER JOIN t_o_dataTemp b ON a.MN=b.MN  where a.MN='" + MN + "'";
            var Data1 = dao.GetList(sql, new string[] { "Name", "Address", "LastTime", "Y1", "V1", "K1","Y2","V2", "K2"});

            sql = "select CONVERT(varchar,DataTime,120) as DataTime,Y1,V1,K1,Y2,V2,K2 from t_o_data where MN='" + MN + "' " +
                " and DataTime>'" + StartTime + "' and DataTime<'" + EndTime + "' order by DataTime ASC";
            var Data2 = dao.GetList(sql, new string[] { "DataTime", "Y1", "V1", "K1", "Y2", "V2", "K2" });

            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 5000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 5000);
            Sheet1.SetColumnWidth(4, 5000);
            Sheet1.SetColumnWidth(5, 5000);
            Sheet1.SetColumnWidth(6, 5000);


            double Sum = 0;
            double tempSum = 0;
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("油烟数据监测统计表");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0.Height = 20 * 20;
            Sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6));
            ////////////////////////////////////////////////////
            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("表格时间");
            row0.CreateCell(1).SetCellValue(DateTime.Now.ToString("yyyy-MM-dd mm:HH:ss"));
            row0.CreateCell(2).SetCellValue("状态");
            if(DateTime.Parse(Data1[0]["LastTime"].ToString()) > DateTime.Now.AddHours(-1))
            {
                row0.CreateCell(3).SetCellValue("在线");
            }
            else
            {
                row0.CreateCell(3).SetCellValue("离线");
            } 
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("设备名称");
            row0.CreateCell(1).SetCellValue(Data1[0]["Name"].ToString());
            row0.CreateCell(2).SetCellValue("设备地址");
            row0.CreateCell(3).SetCellValue(Data1[0]["Address"].ToString());
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("通道1");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("通道2");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("油烟浓度");
            row0.CreateCell(1).SetCellValue(Data1[0]["Y1"].ToString());
            row0.CreateCell(2).SetCellValue("油烟浓度");
            row0.CreateCell(3).SetCellValue(Data1[0]["Y2"].ToString());
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("颗粒物");
            row0.CreateCell(1).SetCellValue(Data1[0]["K1"].ToString());
            row0.CreateCell(2).SetCellValue("颗粒物");
            row0.CreateCell(3).SetCellValue(Data1[0]["K2"].ToString());
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("非甲烷总烃");
            row0.CreateCell(1).SetCellValue(Data1[0]["V1"].ToString());
            row0.CreateCell(2).SetCellValue("非甲烷总烃");
            row0.CreateCell(3).SetCellValue(Data1[0]["V2"].ToString());
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");

            row0 = Sheet1.CreateRow(currRow);   //创建行
            currRow++;
            row0.CreateCell(0).SetCellValue("采集时间");
            row0.CreateCell(1).SetCellValue("油烟浓度(通道1)");
            row0.CreateCell(2).SetCellValue("非甲烷总烃(通道1)");
            row0.CreateCell(3).SetCellValue("颗粒物(通道1)");
            row0.CreateCell(4).SetCellValue("油烟浓度(通道2)");
            row0.CreateCell(5).SetCellValue("非甲烷总烃(通道2)");
            row0.CreateCell(6).SetCellValue("颗粒物(通道2)");

            for (int i = 0; i < Data2.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(Data2[i]["DataTime"].ToString());
                row0.CreateCell(1).SetCellValue(Data2[i]["Y1"].ToString());
                row0.CreateCell(2).SetCellValue(Data2[i]["V1"].ToString());
                row0.CreateCell(3).SetCellValue(Data2[i]["K1"].ToString());
                row0.CreateCell(4).SetCellValue(Data2[i]["Y2"].ToString());
                row0.CreateCell(5).SetCellValue(Data2[i]["V2"].ToString());
                row0.CreateCell(6).SetCellValue(Data2[i]["K2"].ToString());
            }



            //刷新各行格式
            ICell commCell;

            for (int j = 0; j < 7; j++)
            {
                commCell = Sheet1.GetRow(0).GetCell(j);
                //CommCellStyle(book, commCell);
                commCell.CellStyle = fCellStyle;

            }

            for (int i = 8; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 7; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return book;
        }

    }

    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }

}